﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;

namespace DAL_Builder
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
            build();
			//kk gg gg  ff ee
        }

        public void build(){
            //string sqlConnStr = "Data Source=111.230.182.245,1433;Initial Catalog=whiteGame;User ID=sa;Password=wangbudong;MultipleActiveResultSets=True";
            string sqlConnStr = "Data Source=127.0.0.1,1433;Initial Catalog=THTreasureDB;User ID=sa;Password=123456;MultipleActiveResultSets=True";
            //0 连接数据库 
            SqlConnection conn = new SqlConnection(sqlConnStr);
            conn.Open();
            //处理OleDbConnection 
            //OleDbConnection cn = new OleDbConnection("Data Source=111.230.182.245,1433;Initial Catalog=whiteGame;User ID=sa;Password=wangbudong;Provider=SQLOLEDB.1");
            OleDbConnection cn = new OleDbConnection("Data Source=127.0.0.1,1433;Initial Catalog=THTreasureDB;User ID=sa;Password=123456;Provider=SQLOLEDB.1");
            cn.Open();
            //利用OleDbConnection的GetOleDbSchemaTable来获得数据库的结构 
            DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            List<TableInfo> tableInfos = new List<TableInfo>();
            foreach (DataRow dr in dt.Rows)
            {
                string tableName = (string)dr["TABLE_NAME"];                
                string[] restrictionValues = new string[4];
                restrictionValues[0] = null; // Catalog
                restrictionValues[1] = null; // Owner
                restrictionValues[2] = tableName; // Table
                restrictionValues[3] = null; // Column

                TableInfo one = new TableInfo();
                one.Name = tableName;

                one.Fields = new List<Field>();
                string hereSql = "select * from " + tableName;
                using (SqlCommand cmd = new SqlCommand(hereSql, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    var cls = ds.Tables[0].Columns;
                    //因为下处的field顺序错乱，从这里先生成 下处获取type
                    for (int clsIndex = 0; clsIndex < cls.Count; clsIndex++)
                    {
                        
                        one.Fields.Add(new Field() { Name = cls[clsIndex].ColumnName, Type = null });
                    }
                    //主键问题
                    //one.PrimaryKeyName = ds.Tables[0].PrimaryKey[0].ColumnName;
                }


                using (DataTable dtField = conn.GetSchema(SqlClientMetaDataCollectionNames.Columns, restrictionValues))
                {
                    for (int dri = 0; dri < dtField.Rows.Count; dri++)
                    {
                        var drField = dtField.Rows[dri];                        
                        //one.Fields.Add(new Field() { Name = drField["column_name"].ToString(), Type = drField["data_type"].ToString() });                       
                        one.Fields.Where(v => v.Name == drField["column_name"].ToString()).First().Type= drField["data_type"].ToString();
                        //Console.WriteLine(drField["column_name"].ToString());
                        Console.WriteLine(drField["data_type"].ToString());
                    }
                }
                tableInfos.Add(one);
                //List<string> listStr = Ado_Builder.Build_SelectInPage(one);
                //foreach (var str in listStr)
                //{
                //    richTextBox1.AppendText(str + "\n");                    
                //}                
                //Console.WriteLine((String)dr["TABLE_NAME"]);
            }
            //tableInfos = tableInfos.Where(v => v.Name == "ChatLog").ToList();
            foreach (var one in tableInfos)
            {
                List<string> listAdd = Ado_Builder.Build_Add(one);
                foreach (var str in listAdd)
                {
                    richTextBox1.AppendText(str + "\n");
                }
                //List<string> listDelete = Ado_Builder.Build_Delete(one);
                //foreach (var str in listDelete)
                //{
                //    richTextBox1.AppendText(str + "\n");
                //}
                //List<string> listUpdate = Ado_Builder.Build_Update(one);
                //foreach (var str in listUpdate)
                //{
                //    richTextBox1.AppendText(str + "\n");
                //}
                ////List<string> listSelectOne = Ado_Builder.Build_SelectOne(one);
                ////foreach (var str in listSelectOne)
                ////{
                ////    richTextBox1.AppendText(str + "\n");
                ////}
                //List<string> listSelectPage = Ado_Builder.Build_SelectInPage(one);
                //foreach (var str in listSelectPage)
                //{
                //    richTextBox1.AppendText(str + "\n");
                //}
                //List<string> listSelectCount = Ado_Builder.Build_SelectCount(one);
                //foreach (var str in listSelectCount)
                //{
                //    richTextBox1.AppendText(str + "\n");
                //}
                //models
                //List<string> listEntity = Ado_Builder.Build_Entity(one);
                //foreach (var str in listEntity)
                //{
                //    richTextBox2.AppendText(str + "\n");
                //}

                //if (one.Name == "GameScoreLocker")
                //{
                //    List<string> listEntity = Ado_Builder.Build_Entity(one);
                //    foreach (var str in listEntity)
                //    {
                //        richTextBox2.AppendText(str + "\n");
                //    }
                //}



            }

        }


        //public List<Field> GetFileds(string connectionString, string tableName)
        //{
        //    List<Field> _Fields = new List<Field>();
        //    SqlConnection _Connection = new SqlConnection(connectionString);
        //    try
        //    {
        //        _Connection.Open();

        //        string[] restrictionValues = new string[4];
        //        restrictionValues[0] = null; // Catalog
        //        restrictionValues[1] = null; // Owner
        //        restrictionValues[2] = tableName; // Table
        //        restrictionValues[3] = null; // Column

        //        using (DataTable dt = _Connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, restrictionValues))
        //        {
        //            foreach (DataRow dr in dt.Rows)
        //            {
        //                Field field;
        //                field.Name = dr["column_name"].ToString();
        //                field.Type = dr["data_type"].ToString();
        //                _Fields.Add(field);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        _Connection.Dispose();
        //    }

        //    return _Fields;
        //}




        public void ShowDatabaseNames() {



        }

        /// <summary>
        /// 取所有数据库名称
        /// </summary>
        /// <returns></returns>
        //public ArrayList getAllDbName()
        //{
        //    ArrayList dbNameList = new ArrayList();
        //    DataTable dbNameTable = new DataTable();
        //    SqlConnection conn = getSqlConnection("master");
        //    SqlDataAdapter adapter = new SqlDataAdapter("select name from master..sysdatabases", conn);
        //    lock (adapter)
        //    {
        //        adapter.Fill(dbNameTable);
        //    }
        //    foreach (DataRow row in dbNameTable.Rows)
        //    {
        //        dbNameList.Add(row["name"]);
        //    }
        //    conn.Close();
        //    return dbNameList;
        //}

    }
}
